﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SQLite;

namespace ABDLight
{
    public partial class frmSQLiteFiles : Form
    {

        private SQLiteConnection sql_con;
        private SQLiteCommand sql_cmd;
        private SQLiteDataAdapter DB;
        private DataSet DSDB = new DataSet();
        private DataSet DSTab = new DataSet();
        private DataTable DT = new DataTable();


        public frmSQLiteFiles()
        {
            InitializeComponent();
        }

        private void SQLiteFiles_Load(object sender, EventArgs e)
        {
            TableList();
        }
        private void SetConnection()
        {
            string databaseName = ABDLight.Properties.Settings.Default.SQLiteDB;
            sql_con = new SQLiteConnection(string.Format("Data Source={0};", databaseName));
        }

        private void grTables_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            SetConnection();
            sql_con.Open();
            sql_cmd = sql_con.CreateCommand();
            string CommandText = "select * from [" + grTables.SelectedRows[0].Cells["Полный путь"].Value.ToString() + "] ORDER BY rowid LIMIT 100";
            DB = new SQLiteDataAdapter(CommandText, sql_con);
            DSTab.Reset();
            DB.Fill(DSTab);
            DT = DSTab.Tables[0];
            grData.DataSource = DT;
            sql_con.Close();
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            if (grTables.RowCount == 0) return;
            string TabName = grTables.SelectedRows[0].Cells["Полный путь"].Value.ToString();
            SQLiteData.ExecuteQuery("DROP TABLE IF EXISTS [" + TabName + "];");
            SQLiteData.ExecuteQuery("delete from t_TableInfo where [Полный путь] = '" + TabName + "'");

            TableList();
            
        }

        private void btnChoose_Click(object sender, EventArgs e)
        {
            if (grTables.RowCount == 0) return;
            string TabName = grTables.SelectedRows[0].Cells["Полный путь"].Value.ToString();
            ABDLight.Properties.Settings.Default.isLoadFromDB = true;


            ((frmMine)this.Owner).dtMain = SQLiteData.GetDataTable(TabName);

            ((frmMine)this.Owner).dtMain.Columns["isCheck"].Caption = "Прочищен";
            ((frmMine)this.Owner).dtMain.Columns["isChanget"].Caption = "Ручной разбор";
            ((frmMine)this.Owner).dtMain.Columns["isBad"].Caption = "Плохой";
            ((frmMine)this.Owner).dtMain.Columns["textStatus"].Caption = "Статус Разбора";
            ((frmMine)this.Owner).dtMain.Columns["iqWorkAddress"].Caption = "Полный разобранный адрес";
            ((frmMine)this.Owner).dtMain.Columns["iqPostCodeIn"].Caption = "Индекс исходного адреса";
            ((frmMine)this.Owner).dtMain.Columns["iqPostCode"].Caption = "Индекс разобранного адреса";
            ((frmMine)this.Owner).dtMain.Columns["iqFullAddress"].Caption = "Разобранный адрес";
            ((frmMine)this.Owner).dtMain.Columns["iqHouse"].Caption = "Дом";
            ((frmMine)this.Owner).dtMain.Columns["iqCase"].Caption = "Корпус";
            ((frmMine)this.Owner).dtMain.Columns["iqBuild"].Caption = "Строение";
            ((frmMine)this.Owner).dtMain.Columns["iqLiter"].Caption = "Литера";
            ((frmMine)this.Owner).dtMain.Columns["iqVlad"].Caption = "Владение";
            ((frmMine)this.Owner).dtMain.Columns["iqBlock"].Caption = "Блок";
            ((frmMine)this.Owner).dtMain.Columns["iqPom"].Caption = "Помещение";
            ((frmMine)this.Owner).dtMain.Columns["iqPav"].Caption = "Павильон";
            ((frmMine)this.Owner).dtMain.Columns["iqUch"].Caption = "Участок";
            ((frmMine)this.Owner).dtMain.Columns["iqObsch"].Caption = "Общежитие";
            ((frmMine)this.Owner).dtMain.Columns["iqFlat"].Caption = "Квартира";
            ((frmMine)this.Owner).dtMain.Columns["iqKom"].Caption = "Комната";
            ((frmMine)this.Owner).dtMain.Columns["iqOffice"].Caption = "Офис";
            ((frmMine)this.Owner).dtMain.Columns["iqKab"].Caption = "Кабинет";
            ((frmMine)this.Owner).dtMain.Columns["iqAbon"].Caption = "а/я";
            ((frmMine)this.Owner).dtMain.Columns["iqVch"].Caption = "в/ч";
            ((frmMine)this.Owner).dtMain.Columns["iqComment"].Caption = "Комментарий";
            ((frmMine)this.Owner).dtMain.Columns["iqKLADR"].Caption = "КЛАДР";
            ((frmMine)this.Owner).dtMain.Columns["iqLostAddr"].Caption = "Не разобрано";
            ((frmMine)this.Owner).dtMain.Columns["iqError"].Caption = "Ошибка";
            ((frmMine)this.Owner).dtMain.Columns["iqStatus"].Caption = "Статус";
            ((frmMine)this.Owner).dtMain.Columns["iqCountry"].Caption = "Страна";
            ((frmMine)this.Owner).dtMain.Columns["iqRegion"].Caption = "Регион";
            ((frmMine)this.Owner).dtMain.Columns["iqRegionType"].Caption = "Тип региона";
            ((frmMine)this.Owner).dtMain.Columns["iqSubRegion"].Caption = "Район";
            ((frmMine)this.Owner).dtMain.Columns["iqSubRegionType"].Caption = "Тип района";
            ((frmMine)this.Owner).dtMain.Columns["iqCity"].Caption = "Город";
            ((frmMine)this.Owner).dtMain.Columns["iqCityType"].Caption = "Тип города";
            ((frmMine)this.Owner).dtMain.Columns["iqNp"].Caption = "НП";
            ((frmMine)this.Owner).dtMain.Columns["iqNpType"].Caption = "Тип НП";
            ((frmMine)this.Owner).dtMain.Columns["iqStreet"].Caption = "Улица";
            ((frmMine)this.Owner).dtMain.Columns["iqStreetType"].Caption = "Тип улицы";




            ((frmMine)this.Owner).dtVariant.Clear();
            ((frmMine)this.Owner).dtVariant.Columns.Clear();

            DataColumn iqPostCode = new DataColumn();
            iqPostCode.ColumnName = "iqPostCode";
            iqPostCode.Caption = "Индекс разобранного адреса";
            iqPostCode.DataType = System.Type.GetType("System.String");
            ((frmMine)this.Owner).dtVariant.Columns.Add(iqPostCode);

            DataColumn iqFullAddress = new DataColumn();
            iqFullAddress.ColumnName = "iqFullAddress";
            iqFullAddress.Caption = "Разобранный адрес";
            iqFullAddress.DataType = System.Type.GetType("System.String");
            ((frmMine)this.Owner).dtVariant.Columns.Add(iqFullAddress);

            DataColumn iqHouse = new DataColumn();
            iqHouse.ColumnName = "iqHouse";
            iqHouse.Caption = "Дом";
            iqHouse.DataType = System.Type.GetType("System.String");
            ((frmMine)this.Owner).dtVariant.Columns.Add(iqHouse);

            DataColumn iqCase = new DataColumn();
            iqCase.ColumnName = "iqCase";
            iqCase.Caption = "Корпус";
            iqCase.DataType = System.Type.GetType("System.String");
            ((frmMine)this.Owner).dtVariant.Columns.Add(iqCase);

            DataColumn iqBuild = new DataColumn();
            iqBuild.ColumnName = "iqBuild";
            iqBuild.Caption = "Строение";
            iqBuild.DataType = System.Type.GetType("System.String");
            ((frmMine)this.Owner).dtVariant.Columns.Add(iqBuild);

            DataColumn iqLiter = new DataColumn();
            iqLiter.ColumnName = "iqLiter";
            iqLiter.Caption = "Литера";
            iqLiter.DataType = System.Type.GetType("System.String");
            ((frmMine)this.Owner).dtVariant.Columns.Add(iqLiter);

            DataColumn iqVlad = new DataColumn();
            iqVlad.ColumnName = "iqVlad";
            iqVlad.Caption = "Владение";
            iqVlad.DataType = System.Type.GetType("System.String");
            ((frmMine)this.Owner).dtVariant.Columns.Add(iqVlad);

            DataColumn iqBlock = new DataColumn();
            iqBlock.ColumnName = "iqBlock";
            iqBlock.Caption = "Блок";
            iqBlock.DataType = System.Type.GetType("System.String");
            ((frmMine)this.Owner).dtVariant.Columns.Add(iqBlock);

            DataColumn iqPom = new DataColumn();
            iqPom.ColumnName = "iqPom";
            iqPom.Caption = "Помещение";
            iqPom.DataType = System.Type.GetType("System.String");
            ((frmMine)this.Owner).dtVariant.Columns.Add(iqPom);

            DataColumn iqPav = new DataColumn();
            iqPav.ColumnName = "iqPav";
            iqPav.Caption = "Павильон";
            iqPav.DataType = System.Type.GetType("System.String");
            ((frmMine)this.Owner).dtVariant.Columns.Add(iqPav);

            DataColumn iqUch = new DataColumn();
            iqUch.ColumnName = "iqUch";
            iqUch.Caption = "Участок";
            iqUch.DataType = System.Type.GetType("System.String");
            ((frmMine)this.Owner).dtVariant.Columns.Add(iqUch);

            DataColumn iqObsch = new DataColumn();
            iqObsch.ColumnName = "iqObsch";
            iqObsch.Caption = "Общежитие";
            iqObsch.DataType = System.Type.GetType("System.String");
            ((frmMine)this.Owner).dtVariant.Columns.Add(iqObsch);

            DataColumn iqFlat = new DataColumn();
            iqFlat.ColumnName = "iqFlat";
            iqFlat.Caption = "Квартира";
            iqFlat.DataType = System.Type.GetType("System.String");
            ((frmMine)this.Owner).dtVariant.Columns.Add(iqFlat);
            //////////////////////////////////////////////////
            DataColumn iqKom = new DataColumn();
            iqKom.ColumnName = "iqKom";
            iqKom.Caption = "Комната";
            iqKom.DataType = System.Type.GetType("System.String");
            ((frmMine)this.Owner).dtVariant.Columns.Add(iqKom);

            DataColumn iqOffice = new DataColumn();
            iqOffice.ColumnName = "iqOffice";
            iqOffice.Caption = "Офис";
            iqOffice.DataType = System.Type.GetType("System.String");
            ((frmMine)this.Owner).dtVariant.Columns.Add(iqOffice);

            DataColumn iqKab = new DataColumn();
            iqKab.ColumnName = "iqKab";
            iqKab.Caption = "Кабинет";
            iqKab.DataType = System.Type.GetType("System.String");
            ((frmMine)this.Owner).dtVariant.Columns.Add(iqKab);

            DataColumn iqAbon = new DataColumn();
            iqAbon.ColumnName = "iqAbon";
            iqAbon.Caption = "а/я";
            iqAbon.DataType = System.Type.GetType("System.String");
            ((frmMine)this.Owner).dtVariant.Columns.Add(iqAbon);

            DataColumn iqVch = new DataColumn();
            iqVch.ColumnName = "iqVch";
            iqVch.Caption = "в/ч";
            iqVch.DataType = System.Type.GetType("System.String");
            ((frmMine)this.Owner).dtVariant.Columns.Add(iqVch);

            DataColumn iqComment = new DataColumn();
            iqComment.ColumnName = "iqComment";
            iqComment.Caption = "Комментарий";
            iqComment.DataType = System.Type.GetType("System.String");
            ((frmMine)this.Owner).dtVariant.Columns.Add(iqComment);

            DataColumn iqKLADR = new DataColumn();
            iqKLADR.ColumnName = "iqKLADR";
            iqKLADR.Caption = "КЛАДР";
            iqKLADR.DataType = System.Type.GetType("System.String");
            ((frmMine)this.Owner).dtVariant.Columns.Add(iqKLADR);

            DataColumn iqLostAddr = new DataColumn();
            iqLostAddr.ColumnName = "iqLostAddr";
            iqLostAddr.Caption = "Не разобрано";
            iqLostAddr.DataType = System.Type.GetType("System.String");
            ((frmMine)this.Owner).dtVariant.Columns.Add(iqLostAddr);

            DataColumn iqError = new DataColumn();
            iqError.ColumnName = "iqError";
            iqError.Caption = "Ошибка";
            iqError.DataType = System.Type.GetType("System.String");
            ((frmMine)this.Owner).dtVariant.Columns.Add(iqError);

            DataColumn iqStatus = new DataColumn();
            iqStatus.ColumnName = "iqStatus";
            iqStatus.Caption = "Статус";
            iqStatus.DataType = System.Type.GetType("System.String");
            ((frmMine)this.Owner).dtVariant.Columns.Add(iqStatus);

            DataColumn iqCountry = new DataColumn();
            iqCountry.ColumnName = "iqCountry";
            iqCountry.Caption = "Страна";
            iqCountry.DataType = System.Type.GetType("System.String");
            ((frmMine)this.Owner).dtVariant.Columns.Add(iqCountry);

            DataColumn iqRegion = new DataColumn();
            iqRegion.ColumnName = "iqRegion";
            iqRegion.Caption = "Регион";
            iqRegion.DataType = System.Type.GetType("System.String");
            ((frmMine)this.Owner).dtVariant.Columns.Add(iqRegion);

            DataColumn iqRegionType = new DataColumn();
            iqRegionType.ColumnName = "iqRegionType";
            iqRegionType.Caption = "Тип региона";
            iqRegionType.DataType = System.Type.GetType("System.String");
            ((frmMine)this.Owner).dtVariant.Columns.Add(iqRegionType);

            DataColumn iqSubRegion = new DataColumn();
            iqSubRegion.ColumnName = "iqSubRegion";
            iqSubRegion.Caption = "Район";
            iqSubRegion.DataType = System.Type.GetType("System.String");
            ((frmMine)this.Owner).dtVariant.Columns.Add(iqSubRegion);

            DataColumn iqSubRegionType = new DataColumn();
            iqSubRegionType.ColumnName = "iqSubRegionType";
            iqSubRegionType.Caption = "Тип района";
            iqSubRegionType.DataType = System.Type.GetType("System.String");
            ((frmMine)this.Owner).dtVariant.Columns.Add(iqSubRegionType);

            DataColumn iqCity = new DataColumn();
            iqCity.ColumnName = "iqCity";
            iqCity.Caption = "Город";
            iqCity.DataType = System.Type.GetType("System.String");
            ((frmMine)this.Owner).dtVariant.Columns.Add(iqCity);

            DataColumn iqCityType = new DataColumn();
            iqCityType.ColumnName = "iqCityType";
            iqCityType.Caption = "Тип города";
            iqCityType.DataType = System.Type.GetType("System.String");
            ((frmMine)this.Owner).dtVariant.Columns.Add(iqCityType);

            DataColumn iqNp = new DataColumn();
            iqNp.ColumnName = "iqNp";
            iqNp.Caption = "НП";
            iqNp.DataType = System.Type.GetType("System.String");
            ((frmMine)this.Owner).dtVariant.Columns.Add(iqNp);

            DataColumn iqNpType = new DataColumn();
            iqNpType.ColumnName = "iqNpType";
            iqNpType.Caption = "Тип НП";
            iqNpType.DataType = System.Type.GetType("System.String");
            ((frmMine)this.Owner).dtVariant.Columns.Add(iqNpType);

            DataColumn iqStreet = new DataColumn();
            iqStreet.ColumnName = "iqStreet";
            iqStreet.Caption = "Улица";
            iqStreet.DataType = System.Type.GetType("System.String");
            ((frmMine)this.Owner).dtVariant.Columns.Add(iqStreet);

            DataColumn iqStreetType = new DataColumn();
            iqStreetType.ColumnName = "iqStreetType";
            iqStreetType.Caption = "Тип улицы";
            iqStreetType.DataType = System.Type.GetType("System.String");
            ((frmMine)this.Owner).dtVariant.Columns.Add(iqStreetType);

            DataTable SetDT = new DataTable();
            SetDT = SQLiteData.GetSetData(TabName);

            ABDLight.Properties.Settings.Default.CleanFileName = TabName;
            frmClean.cCfg.CheckType = "addrmatch";
            frmClean.cCfg.ErrorRowIndex = -1;
            //frmClean.cCfg.StartInd = Convert.ToInt32(grTables.SelectedRows[0].Cells["Поле чистки"].Value.ToString());
            //,iqStartInd INTEGER,iqnAll INTEGER,iqnGood INTEGER,iqnBad INTEGER,iqnSomn INTEGER,iqStartClean INTEGER
            frmClean.cCfg.StartInd = Convert.ToInt32(SetDT.Rows[0]["iqStartInd"].ToString());
            //frmClean.cCfg.ErrorRowIndex = Convert.ToInt32(SetDT.Rows[0]["iqStartClean"].ToString()) + 1;
            //frmClean.cCfg.nAll = Convert.ToInt32(SetDT.Rows[0]["iqnAll"].ToString());
            //frmClean.cCfg.nGood = Convert.ToInt32(SetDT.Rows[0]["iqnGood"].ToString());
            //frmClean.cCfg.nBad = Convert.ToInt32(SetDT.Rows[0]["iqnBad"].ToString());
            //frmClean.cCfg.nSomn = Convert.ToInt32(SetDT.Rows[0]["iqnSomn"].ToString());

            ((frmMine)this.Owner).OpenFrmClean();
            this.Close();
        }

        private void TableList()
        {
            SetConnection();
            sql_con.Open();
            sql_cmd = sql_con.CreateCommand();
            //string CommandText = "select * from sqlite_master where type = 'table'";
            string CommandText = "select [Имя файла],[Полный путь],[Дата] from t_TableInfo";
            DB = new SQLiteDataAdapter(CommandText, sql_con);
            DSDB.Reset();
            DB.Fill(DSDB);
            DT = DSDB.Tables[0];
            grTables.DataSource = DT;
            sql_con.Close();

        }

        private void btnDelAll_Click(object sender, EventArgs e)
        {
            SQLiteData.ExecuteQuery("delete from t_TableInfo");
            TableList();
        }

    }
}
